package bancosys.tec.utils.db.executor;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import jmine.tec.utils.UtilsMessages;
import bancosys.tec.utils.db.CouldNotProcessStatementException;

/**
 * @author Marcio Ribeiro (mmr)
 * @created Apr 26, 2007
 */
public class OracleDbExecutor extends JdbcDbExecutor {

    private static final String INSERT = "INSERT";

    private static final Pattern PATTERN = Pattern.compile(".*[^\\\\](('.*[^\\\\]')|('')).*", Pattern.MULTILINE | Pattern.DOTALL);

    /**
     * @return o char delimitador
     */
    @Override
    public char getDelimiterChar() {
        return ';';
    }

    /**
     * executa o drop das tabelas
     * 
     * @param conn Connection
     * @throws CouldNotExecuteException se não for possível executar
     */
    @Override
    protected void executeDrop(Connection conn) throws CouldNotExecuteException {
        LOG.info("Executando drop das tabelas, views e sequences");
        List<String> tables = new ArrayList<String>();
        Statement sqlSt;
        try {
            sqlSt = conn.createStatement();
            ResultSet rs = sqlSt.executeQuery("SELECT TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME");
            while (rs.next()) {
                tables.add(rs.getString(1));
            }
            sqlSt.close();
        } catch (SQLException e) {
            throw new CouldNotExecuteException(UtilsMessages.ORACLE_UNABLE_TO_QUERY_TABLES.create(), e);
        }

        for (String table : tables) {
            try {
                LOG.debug("Dropando tabela " + table);
                sqlSt = conn.createStatement();
                sqlSt.executeUpdate("DROP TABLE " + table + " CASCADE CONSTRAINT");
                sqlSt.executeUpdate("PURGE TABLE " + table);
                sqlSt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        // drop view
        tables.clear();
        try {
            sqlSt = conn.createStatement();
            ResultSet rs = sqlSt.executeQuery("SELECT VIEW_NAME FROM USER_VIEWS ORDER BY VIEW_NAME");
            while (rs.next()) {
                tables.add(rs.getString(1));
            }
            sqlSt.close();
        } catch (SQLException e) {
            throw new CouldNotExecuteException(UtilsMessages.ORACLE_UNABLE_TO_QUERY_VIEWS.create(), e);
        }

        for (String table : tables) {
            try {
                LOG.debug("Dropando view " + table);
                sqlSt = conn.createStatement();
                sqlSt.executeUpdate("DROP VIEW " + table);
                sqlSt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        // drop sequence
        tables.clear();
        try {
            sqlSt = conn.createStatement();
            ResultSet rs = sqlSt.executeQuery("SELECT SEQUENCE_NAME FROM USER_SEQUENCES ORDER BY SEQUENCE_NAME");
            while (rs.next()) {
                tables.add(rs.getString(1));
            }
            sqlSt.close();
        } catch (SQLException e) {
            throw new CouldNotExecuteException(UtilsMessages.ORACLE_UNABLE_TO_QUERY_SEQUENCES.create(), e);
        }

        for (String table : tables) {
            try {
                LOG.debug("Dropando sequence " + table);
                sqlSt = conn.createStatement();
                sqlSt.executeUpdate("DROP SEQUENCE " + table);
                sqlSt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        LOG.info("Finalizando drop das tabelas, views e sequences");
    }

    /**
     * executa os arquivos
     * 
     * @param conn a conexão
     * @throws IOException se não for possível ler do arquivo
     * @throws CouldNotProcessStatementException se não for possível processar
     * @throws SQLException se não for possível executar um comando SQL
     */
    @Override
    protected void executeFiles(Connection conn) throws IOException, CouldNotProcessStatementException, SQLException {
        Statement sqlSt;
        sqlSt = conn.createStatement();
        sqlSt.executeUpdate("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'");
        sqlSt.close();

        sqlSt = conn.createStatement();
        sqlSt.executeUpdate("ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS'");
        sqlSt.close();

        super.executeFiles(conn);
    }

    /**
     * Prepares a statement with the given str as the sql command statement.
     * 
     * @param statement statement
     * @return PreparedStatement
     * @throws SQLException SQLException
     */
    @Override
    protected PreparedStatement createStatement(String statement) throws SQLException {
        if (statement.length() > INSERT.length() && statement.substring(0, INSERT.length()).equalsIgnoreCase(INSERT)) {
            return this.doCreateStatement(statement, new ArrayList<String>());
        } else {
            return super.createStatement(statement);
        }
    }

    /**
     * Prepares a statement with the given str as the sql command statement.
     * 
     * @param statement statement
     * @param parameters list of parameters, must come empty on call.
     * @return PreparedStatement
     * @throws SQLException SQLException
     */
    private PreparedStatement doCreateStatement(String statement, List<String> parameters) throws SQLException {
        Matcher matcher = PATTERN.matcher(statement);
        if (matcher.matches()) {
            String str = matcher.group(1);
            String refinedStatement = statement.substring(0, matcher.start(1)) + "?" + statement.substring(matcher.end(1));
            parameters.add(0, str.substring(1, str.length() - 1));
            return this.doCreateStatement(refinedStatement, parameters);
        } else {
            PreparedStatement ps = this.getConnection().prepareStatement(statement);
            for (int i = 0; i < parameters.size(); i++) {
                ps.setString(i + 1, parameters.get(i));
            }
            return ps;
        }
    }
}